package com.bondqin.sakila.dao.impl;

import java.math.BigDecimal;
import java.util.Date;

import javax.persistence.EntityManager;
import javax.persistence.ParameterMode;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.StoredProcedureQuery;

import org.springframework.stereotype.Component;

import com.bondqin.sakila.dao.CommonDao;

@Component
public class CommonDaoImpl implements CommonDao {

	@PersistenceContext
	private EntityManager em;
	
	@Override
	public BigDecimal getCustomerBalance(Integer customerId, Date effectiveDate) {
		Query query = em.createNativeQuery("SELECT get_customer_balance(?, ?) ");
		query.setParameter(1, customerId);
		query.setParameter(2, effectiveDate);
		
		BigDecimal result = (BigDecimal)query.getSingleResult();
		
		return result;
	}

	@Override
	public Integer inventoryHeldByCustomer(Integer inventoryId) {
		Query query = em.createNativeQuery("SELECT inventory_held_by_customer(?) ");
		query.setParameter(1, inventoryId);
		
		Integer result = (Integer)query.getSingleResult();
		
		return result;
	}

	@Override
	public Boolean inventoryInStock(Integer inventoryId) {
		Query query = em.createNativeQuery("SELECT inventory_in_stock(?) ");
		query.setParameter(1, inventoryId);
		
		Boolean result = (Boolean)query.getSingleResult();
		
		return result;
	}

	@Override
	public Integer filmInStock(Integer filmId, Integer storeId) {
		StoredProcedureQuery query = em.createStoredProcedureQuery("film_in_stock");
		query.registerStoredProcedureParameter("p_film_id", Integer.class, ParameterMode.IN);
		query.registerStoredProcedureParameter("p_store_id", Integer.class, ParameterMode.IN);
		query.registerStoredProcedureParameter("p_film_count", Integer.class, ParameterMode.OUT);
		
		query.setParameter("p_film_id", filmId);
		query.setParameter("p_store_id", storeId);
        // execute SP
		query.execute();
        // get result
		Integer result = (Integer)query.getOutputParameterValue("p_film_count");
        
		return result;
	}

	@Override
	public Integer filmNotInStock(Integer filmId, Integer storeId) {
		StoredProcedureQuery query = em.createStoredProcedureQuery("film_not_in_stock");
		query.registerStoredProcedureParameter(
				"p_film_id", Integer.class, ParameterMode.IN);
		query.registerStoredProcedureParameter(
				"p_store_id", Integer.class, ParameterMode.IN);
		query.registerStoredProcedureParameter(
				"p_film_count", Integer.class, ParameterMode.OUT);
		
		query.setParameter("p_film_id", filmId);
		query.setParameter("p_store_id", storeId);
        // execute SP
		query.execute();
        // get result
		Integer result = (Integer)query.getOutputParameterValue("p_film_count");
        
		return result;
	}

	@Override
	public Integer rewardsReport(Integer minMonthlyPurchases, BigDecimal minDollarAmountPurchased) {
		StoredProcedureQuery query = em.createStoredProcedureQuery("film_not_in_stock");
		query.registerStoredProcedureParameter(
				"min_monthly_purchases", Integer.class, ParameterMode.IN);
		query.registerStoredProcedureParameter(
				"min_dollar_amount_purchased", BigDecimal.class, ParameterMode.IN);
		query.registerStoredProcedureParameter(
				"count_rewardees", Integer.class, ParameterMode.OUT);
		
		query.setParameter("min_monthly_purchases", minMonthlyPurchases);
		query.setParameter("min_dollar_amount_purchased", minDollarAmountPurchased);
        // execute SP
		query.execute();
        // get result
		Integer result = (Integer)query.getOutputParameterValue("count_rewardees");
        
		return result;
	}
}
